package cn.thoughtworks.school.repositories;

import cn.thoughtworks.school.entities.Application;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Map;

@Repository
public interface ApplicationRepository extends JpaRepository<Application, Long> {
  @Query(value = "select id,userId,(" +
    "case `type` " +
    "when 'serial' then (select title from serial where a.resourceId=id and organizationId=?1) " +
    "else (select title from video where a.resourceId=id  and organizationId=?1) end " +
    ") as title,reason,status,`type`,createTime from application a where a.status = '申请中' order by createTime DESC", nativeQuery = true)
  Page<Map> getMyNewSerialsApplications(Long currentOrganizationId, Pageable pageable);

  @Query(value = "select id,userId,(" +
    "case `type` " +
    "when 'serial' then (select title from serial where a.resourceId=id and organizationId=?1) " +
    "else (select title from video where a.resourceId=id  and organizationId=?1) end " +
    ") as title,reason,status,`type`,createTime from application a where a.status != '申请中' order by createTime DESC", nativeQuery = true)
  Page<Map> getMyUnHandledSerialsApplications(Long currentOrganizationId, Pageable pageable);

  @Query(value = "select id,userId,(" +
    "case `type` " +
    "when 'serial' then (select title from serial where a.resourceId=id ) " +
    "else (select title from video where a.resourceId=id) end " +
    ") as title,reason,status,`type`,createTime from application a where a.userId= ?1 order by createTime DESC", nativeQuery = true)
  Page<Map> getApplicationsUserId(Long id, Pageable pageable);

}
